import { Application } from 'egg'
import { Sequelize } from 'sequelize'
import { UnitOfWork } from './unit-of-work'

export class SqlReport {
  app: Application
  model: Sequelize
  constructor(app: Application, uow: UnitOfWork) {
    this.app = app
    this.model = app.model
  }

  async execSql(sql, noconsole: boolean = false) {
    const options = {}
    return new Promise((resolve, reject) => {
      // this.app.logger.info('execSql', sql)
      this.model.query(sql, options)
        .spread((results, metadata) => {
          if (noconsole) {
            const fCount = metadata['fields'] ? metadata['fields'].length : -1
            this.app.logger.info(`query metadata: ${metadata['command']} row ${metadata['rowCount']} fields ${fCount}`)
          }
          resolve(results)
        })
        .catch(err => reject(err))
    })
  }

  buildCondition(query: { where, order, offset, limit }) {
    const { where, order, offset, limit } = query
    const { querySettings } = this.app.config.settings
    let condition = ''
    if (where) {
      let whereObj = where
      try {
        whereObj = JSON.parse(whereObj)
      } catch (err) {
        //
      }
      if (typeof whereObj === 'string') condition += `where ${whereObj}`
      else if (Array.isArray(whereObj) && whereObj.length > 0) condition += `where ${whereObj.join(' and ')}`
      else {
        let whereSql = ''
        Object.keys(whereObj).forEach(field => {
          const val = whereObj[field]
          if (whereSql) whereSql += ' and '
          if (val === null) whereSql += `"${field}" is null`
          else if (typeof val === 'string') whereSql += `"${field}"='${val}'`
          else if (typeof val === 'number' || typeof val === 'boolean') whereSql += `"${field}"=${val}`
          else {
            let fieldWhereSql = ''
            Object.keys(val).forEach(op => {
              let opSql = op.startsWith('$') ? op.substring(1) : '='
              opSql = opSql.toLowerCase()
              if (opSql === 'like' || opSql === 'ilike') {
                //
              } else if (opSql === 'in') {
                //
              } else if (opSql === 'eq') opSql = '='
              else if (opSql === 'ne') opSql = '<>'
              else if (opSql === 'gte') opSql = '>='
              else if (opSql === 'gt') opSql = '>'
              else if (opSql === 'lte') opSql = '<='
              else if (opSql === 'lt') opSql = '<'
              else throw new Error(`op '${op}' is not implement.`)

              const opVal = val[op]
              let valSql = ''
              if (opVal === null) {
                if (opSql === '=') opSql = 'is'
                else if (opSql === '<>') opSql = 'is not'
                else throw new Error(`op '${op}' not allow 'null' value.`)
                valSql = 'null'
              } else if (typeof opVal === 'number' || typeof opVal === 'boolean') {
                valSql = `${opVal}`
              } else if (Array.isArray(opVal) && opVal.length > 0) {
                if (opSql === 'in') {
                  valSql = typeof opVal[0] === 'number' ? `(${opVal.join(',')})` : `('${opVal.join('\',\'')}')`
                } else throw new Error(`op '${op}' not allow 'array' value`)
              } else valSql = `'${opVal}'`
              // console.log(field, opSql, valSql)

              fieldWhereSql += ` ${fieldWhereSql ? 'and ' : ''}"${field}" ${opSql} ${valSql}`
            })
            whereSql += `(${fieldWhereSql})`
          }
        })
        condition += `where ${whereSql}`
      }
    }

    let orderObj = order
    try {
      orderObj = JSON.parse(orderObj)
    } catch (err) {
      //
    }
    if (Array.isArray(orderObj) && orderObj.length > 0) {
      const orderSql = orderObj.map(oa => (Array.isArray(oa) ? `"${oa[0]}" ${oa[1]}` : `"${oa}"`)).join(',')
      condition += ` order by ${orderSql}`
    }

    if (limit > 0) condition += ` limit ${limit}`
    else condition += ` limit ${querySettings.limit}`
    if (offset > 0) condition += ` offset ${offset}`

    // console.log(condition, limit)
    return condition
  }

  async execMainSql(mainSql, query) {
    const sql = `select *
    from (${mainSql}) x
    ${this.buildCondition(query)}`
    return this.execSql(sql)
  }

  async execCountSql(mainSql, query) {
    const { querySettings } = this.app.config.settings
    const countQuery = {
      where: query ? query.where : null,
      order: null,
      offset: 0,
      limit: querySettings.max,
    }
    const sql = `select count(*) "count"
    from (${mainSql}) xx
    ${this.buildCondition(countQuery)}`
    return this.execSql(sql, true)
  }

  async execByQuery(mainSql, query) {
    if (query && query.nocount) return this.execMainSql(mainSql, query)

    const countRows = await this.execCountSql(mainSql, query)
    const rows = await this.execMainSql(mainSql, query)
    return { count: countRows[0].count, rows }
  }

  async test(query) {
    const sql = 'select status,count(*) as count from "purchaseOrders" group by status'
    return this.execByQuery(sql, query)
  }
}
